package com.supermap.dataHandling.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * Excel工具类
 */
public class ExcelUtil {

    /**
     * 获取excel文件所有内容
     * @param dir
     * @return
     */
    public static List<List<String>> readExcels(String dir){
        try {
            String encoding = "UTF-8";
            File excel = new File(dir);
            List<List<String>> excelList = new ArrayList<>();
            if (excel.isFile() && excel.exists()) {
                String[] split = excel.getName().split("\\.");
                Workbook wb = null;
                //根据文件后缀名判断
                if ("xls".equals(split[1])) {
                    FileInputStream fis = new FileInputStream(excel);
                    wb = new HSSFWorkbook(fis);
                } else if ("xlsx".equals(split[1])) {
                    wb = new XSSFWorkbook(OPCPackage.open(excel));
                } else {
                    return null;
                }
                //获取工作簿
                Sheet sheet = wb.getSheetAt(0);
                int firstRowIndex = sheet.getFirstRowNum() + 1;
                int lastRowIndex = sheet.getLastRowNum() + 1;
                //循环行
                for (int rowIndex = firstRowIndex; rowIndex < lastRowIndex; rowIndex++) {
                    Row row = sheet.getRow(rowIndex);
                    List<String> rowList = new ArrayList<>();
                    if (row != null) {
                        int firstCellIndex = row.getFirstCellNum();
                        int lastCellIndex = row.getLastCellNum();
                        //循环每行单元格
                        for (int cellIndex = firstCellIndex; cellIndex < lastCellIndex; cellIndex++) {
                            Cell cell = row.getCell(cellIndex);
                            if (cell != null) {
                                cell.setCellType(1);
                                rowList.add(cell.toString());
                            }
                        }
                    }
                    excelList.add(rowList);
                }
                return excelList;
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 转置输出excel中的内容
     * @param lists
     * @return
     */
    public static List<List<String>> formatExcel(List<List<String>> lists){
        if (lists.size() == 0){
            return null;
        }
        List<List<String>> formatList = new ArrayList<>();
        int size = lists.get(0).size();

        int k = 0;
        for (int j = 0; j<size; j++){
            List<String> newList = new ArrayList<>();
            for (int i = 0;i<lists.size();i++){
                String s = lists.get(i).get(k);
                newList.add(s);
            }
            k++;
            formatList.add(newList);
        }

        return formatList;
    }




}
